package day1130;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;


public class StudentDao {
	//增加
		public String insert(StudentInfo s) {
			Connection conn = JDBCUtil.getConnection();
			String sql = "insert into student(id,name,major) values(?,?,?)";
			PreparedStatement ps = null;
			try {
				ps = conn.prepareCall(sql);
				ps.setString(1, s.getId());
				ps.setString(2, s.getName());
				ps.setString(3, s.getMajor());
				int res = ps.executeUpdate();
				return res+"";
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}finally {
				JDBCUtil.close(conn, ps);
			}
			return null;
		}
		
		//按照学号删除学生信息
		public static Integer delete(String id) {
			Connection conn = JDBCUtil.getConnection();
			String sql = "delete from student where id = ?";
			PreparedStatement ps = null;
			try {
				ps = conn.prepareStatement(sql);
				ps.setString(1, id);
				int res = ps.executeUpdate();
				return res;
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}finally {
				JDBCUtil.close(conn, ps);
			}
			return null;
		}
		
		//查询按照姓名
		public static ArrayList<StudentInfo> selectName(String name) {
			List<Object> vals = new ArrayList<>();
			ArrayList<StudentInfo> list = new ArrayList<>();
			Connection conn = JDBCUtil.getConnection();
			StringBuffer sql = new StringBuffer("select * from student where name = ?");
			
			PreparedStatement ps = null;
			ResultSet result = null;
			try {
				    ps = conn.prepareStatement(sql.toString());
					ps.setObject(1, name);
					result = ps.executeQuery();
				while(result.next()) {
					StudentInfo ss = new StudentInfo();
					ss.setId(result.getString("id"));
					ss.setName(result.getString("name"));
					ss.setMajor(result.getString("major"));
					list.add(ss);
				}
				return list;
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			return null;
		}
		
		//查询按照专业
				public static ArrayList<StudentInfo> selectMajor(String major) {
					List<Object> vals = new ArrayList<>();
					ArrayList<StudentInfo> list = new ArrayList<>();
					Connection conn = JDBCUtil.getConnection();
					StringBuffer sql = new StringBuffer("select * from student where major = ?");
					
					PreparedStatement ps = null;
					ResultSet result = null;
					try {
						    ps = conn.prepareStatement(sql.toString());
							ps.setObject(1, major);
							result = ps.executeQuery();
						while(result.next()) {
							StudentInfo ss = new StudentInfo();
							ss.setId(result.getString("id"));
							ss.setName(result.getString("name"));
							ss.setMajor(result.getString("major"));
							list.add(ss);
						}
						return list;
					} catch (SQLException e) {
						// TODO Auto-generated catch block
						e.printStackTrace();
					}
					return null;
				}
				
	//查询学号是否存在
				public static ArrayList<StudentInfo> selectID(String id) {
					List<Object> vals = new ArrayList<>();
					ArrayList<StudentInfo> list = new ArrayList<>();
					Connection conn = JDBCUtil.getConnection();
					StringBuffer sql = new StringBuffer("select * from student where id = ?");					
					PreparedStatement ps = null;
					ResultSet result = null;
					
					try {
						ps = conn.prepareStatement(sql.toString());
						ps.setObject(1, id);
						result = ps.executeQuery();
						while(result.next()) {
							StudentInfo ss = new StudentInfo();
							ss.setId(result.getString("id"));
							ss.setName(result.getString("name"));
							ss.setMajor(result.getString("major"));
							list.add(ss);
						}
						return list;
					} catch (SQLException e) {
						// TODO Auto-generated catch block
						e.printStackTrace();
					}
					return null;
				}
	//查询全部学生的信息
				public static ArrayList<StudentInfo> selectAll() {
					ArrayList<StudentInfo> list = new ArrayList<>();
					String sql = "select * from student";
					Connection conn = JDBCUtil.getConnection();
					PreparedStatement ps = null;
					ResultSet rs = null;
					try {
						ps = conn.prepareStatement(sql);
						rs = ps.executeQuery();
						while(rs.next()) {
							StudentInfo s = new StudentInfo();
							s.setId(rs.getString("id"));
							s.setName(rs.getString("name"));
							s.setMajor(rs.getString("major"));
							list.add(s);
						}
						return list;
					} catch (SQLException e) {
						// TODO Auto-generated catch block
						e.printStackTrace();
					}finally {
						JDBCUtil.close(conn, ps);
					}
					
					return null;
				}

}
